<?php

/**
 * Export Product data to xml data
 *
 * @category   Magestore
 * @package    Magestore_Mageconnector
 * @author     Nguyen Minh Dung (http://oss.com.vn)
 */
class Magestore_Mageconnector_Model_Product extends Magestore_Mageconnector_Model_Abstract {

    /**
     *
     * @param Mage_Catalog_Model_Product $product
     * @param string $baseUrl
     * @param string $storeCode
     * @param int $i
     * @param bool $isShowAllDetails
     * @return string
     */
    public function exportXMLProduct(Mage_Catalog_Model_Product $product, $baseUrl, $storeCode, &$i, $isShowAllDetails = false) {
        try {
            $xml = '';
            $inventory = Mage::getModel('cataloginventory/stock_item')
                            ->loadByProduct($product);

            if ($product->getStatus()) {
                $xml .= '<product order="#' . (++$i) . '">'
                        . '<id>' . $product->getId() . '</id>'
                        . '<name><![CDATA[' . $product->getName() . ']]></name>';

                if ($product->getTypeId() != 'grouped') {
                    $real_price = $product->getPrice();

                    if ($real_price > 0) {
                        $price = Mage::helper('core')
                                        ->currency(Mage::helper('tax')
                                                ->getPrice(
                                                        $product,
                                                        $product->getPrice()
                                                )
                                                , true, false);

                        $now = date('Y-m-d');
                        $special_from_date = split(' ', $product->getSpecialFromDate());
                        $special_from_date = $special_from_date[0];
                        $special_to_date = split(' ', $product->getSpecialToDate());
                        $special_to_date = $special_to_date[0];

                        $xml .= '<price><![CDATA[' . $price . ']]></price>';

                        if ($now >= $special_from_date && $now <= $special_to_date &&
                                $product->getSpecialPrice() < $product->getPrice()) {
                            $special_price = Mage::helper('tax')
                                            ->getPrice(
                                                    $product,
                                                    $product->getSpecialPrice()
                            );

                            if ($product->getTypeId() == 'bundle') {
                                $special_price = $special_price / 100 * $real_price;
                            }
                            $real_price = $special_price;

                            $special_price = Mage::helper('core')
                                            ->currency($special_price, true, false);

                            $xml .= '<special_price><![CDATA[' . $special_price . ']]></special_price>'
                                    . '<special_from_date>' . $special_from_date . '</special_from_date>'
                                    . '<special_to_date>' . $special_to_date . '</special_to_date>';
                        } else if ($product->getFinalPrice() < $product->getPrice()) {
                            $special_price = Mage::helper('tax')
                                            ->getPrice(
                                                    $product,
                                                    $product->getFinalPrice()
                            );

                            if ($product->getTypeId() == 'bundle') {
                                $special_price = $special_price / 100 * $real_price;
                            }
                            $real_price = $special_price;

                            $special_price = Mage::helper('core')
                                            ->currency($special_price, true, false);

                            $xml .= '<special_price><![CDATA[' . $special_price . ']]></special_price>';
                        }
                    }
                    if ($isShowAllDetails) {
                        $xml .= $this->addCustomOptions($product, $real_price)
                                . $this->addPriceTiers($product, $real_price);
                    }
                }

                $xml .= '<url_key><![CDATA[' . $product->getUrlKey() . ']]></url_key>'
                        . '<url><![CDATA[' . $product->getProductUrl() . ']]></url>'
                        . '<short_description><![CDATA[' . $product->getShortDescription() . ']]></short_description>'
                        . '<is_in_stock>' . $product->isInStock() . '</is_in_stock>'
                        . '<is_salable>' . $product->isSalable() . '</is_salable>'
                        . '<image><![CDATA[' . $product->getImageUrl() . ']]></image>'
                        . '<image_label><![CDATA[' . $product->getImageLabel() . ']]></image_label>';

                $listImage = $product->getMediaGalleryImages();
                $gallery = '';
                foreach ($listImage as $image) {
                    $gallery .= '<image>'
                            . '<src><![CDATA[' . $image->getUrl() . ']]></src>'
                            . '<label><![CDATA[' . $image->getLabel() . ']]></label>'
                            . '</image>';
                }
                $xml .= '<image_gallery>' . $gallery . '</image_gallery>';

                if ($isShowAllDetails) {
                    $xml .= '<description><![CDATA[' . $product->getDescription() . ']]></description>';

                    switch ($product->getTypeId()) {
                        case 'grouped':
                            $xml .= $this->addAssociatedProducts($product);
                            break;
                        case 'downloadable':
                            $xml .= $this->addLinks($product);
                            break;
                        case 'configurable':
                            $xml .= $this->addConfigurationAttributes($product, $real_price);
                            break;
                    }
                    $xml .= $this->addMoreInformation($product);
                }

                $xml .= '</product>';
            }
            return $xml;
        } catch (Exception $e) {
            Zend_Debug::dump($e);
            die();
        }
    }

    public function addLinks(Mage_Catalog_Model_Product $product) {
        $xml = '<downloadable_links>';
        try {
            $xml .= '<links_purchased_separately>' . $product->getLinksPurchasedSeparately() . '</links_purchased_separately>'
                    . '<purchased_links_title>' . $product->getLinksTitle() . '</purchased_links_title>'
                    . '<links_form_key>links[]</links_form_key>'
                    . '<purchased_links>';
            $links = $product->getTypeInstance(true)->getLinks($product);
            foreach ($links as $item) {
                if ($item instanceof Mage_Downloadable_Model_Link) {
                    $xml .= '<link><title><![CDATA[' . $item->getTitle() . ']]></title>';
                    if (floatval($item->getPrice()) != 0) {
                        $p = Mage::helper('core')
                                        ->currency($item->getPrice(), true, false);
                        if (substr($p, 0, 1) != '-') {
                            $p = '+' . $p;
                        }

                        $xml .= '<option_price><![CDATA[' . $p . ']]></option_price>';
                    }
                    $xml .= '<form_value>' . $item->getId() . '</form_value>'
                            . '</link>';
                }
            }
            $xml .= '</purchased_links>'
                    . '<sample_links_title>' . $product->getSamplesTitle() . '</sample_links_title>'
                    . '<sample_links>';
            $samples = $product->getTypeInstance(true)->getSamples($product);
            foreach ($samples as $item) {
                if ($item instanceof Mage_Downloadable_Model_Sample) {
                    $xml .= '<link>'
                            . '<title><![CDATA[' . $item->getTitle() . ']]></title>'
                            . '<url><![CDATA[' . Mage::getBaseUrl() . 'downloadable/download/sample/sample_id/' . $item->getId() . ']]></url>'
                            . '</link>';
                }
            }
            $xml .= '</sample_links>';
        } catch (Exception $e) {
            
        }
        $xml .= '</downloadable_links>';
        return $xml;
    }

    public function addMoreInformation(Mage_Catalog_Model_Product $product) {
        $xml = '<more_attributes>';
        try {
            $data = $product->getData();

            foreach ($data as $attrCode => $v) {
                $attribute = $product->getResource()->getAttribute($attrCode);

                if ($attribute instanceof Mage_Catalog_Model_Resource_Eav_Attribute) {
                    if ($attribute->getIsVisibleOnFront()) {
                        $value = $attribute->getFrontend()->getValue($product);
                        $label = $attribute->getFrontendLabel();

                        // TODO this is temporary skipping eco taxes
                        if (is_string($value)) {
                            if (strlen($value) && $product->hasData($attrCode)) {
                                if ($attribute->getFrontendInput() == 'price') {
                                    $value = Mage::app()->getStore($product->getStoreId())->convertPrice($value, true);
                                } else {
                                    $value = $value;
                                }
                                $xml .= '<attribute><label><![CDATA[' . $label . ']]></label>'
                                        . '<value><![CDATA[' . $value . ']]></value></attribute>';
                            }
                        }
                    }
                }
            }
        } catch (Exception $e) {
            Zend_Debug::dump($e);
        }

        $xml .= '</more_attributes>';
        return $xml;
    }

    /**
     * Get list associated produts of grouped product
     *
     * @param Mage_Catalog_Model_Product $product
     */
    public function addAssociatedProducts(Mage_Catalog_Model_Product $product) {
        $xml = '<associated_products>';

        $grouped = $product->getTypeInstance(true);
        $listAssociatedProducts = $grouped->getAssociatedProducts($product);
        $i = 0;
        foreach ($listAssociatedProducts as $associatedProduct) {
            if ($associatedProduct->getStatus()) {
                $xml .= '<associated_product>'
                        . '<order>#' . ++$i . '</order>'
                        . '<id>' . $associatedProduct->getId() . '</id>'
                        . '<name><![CDATA[' . $associatedProduct->getName() . ']]></name>';

                $real_price = $associatedProduct->getPrice();

                $price = Mage::helper('core')
                                ->currency(Mage::helper('tax')
                                        ->getPrice(
                                                $associatedProduct,
                                                $associatedProduct->getPrice()
                                        )
                                        , true, false);

                $now = date('Y-m-d');
                $special_from_date = split(' ', $associatedProduct->getSpecialFromDate());
                $special_from_date = $special_from_date[0];
                $special_to_date = split(' ', $associatedProduct->getSpecialToDate());
                $special_to_date = $special_to_date[0];

                $xml .= '<price><![CDATA[' . $price . ']]></price>';

                if ($now >= $special_from_date && $now <= $special_to_date) {
                    $special_price = Mage::helper('tax')
                                    ->getPrice(
                                            $associatedProduct,
                                            $associatedProduct->getSpecialPrice()
                    );
                    $real_price = $special_price;

                    $special_price = Mage::helper('core')
                                    ->currency($special_price, true, false);

                    $xml .= '<special_price><![CDATA[' . $special_price . ']]></special_price>'
                            . '<special_from_date>' . $special_from_date . '</special_from_date>'
                            . '<special_to_date>' . $special_to_date . '</special_to_date>';
                }

                $xml .= '<url_key><![CDATA[' . $associatedProduct->getUrlKey() . ']]></url_key>'
                        . '<url><![CDATA[' . $associatedProduct->getProductUrl() . ']]></url>'
                        . '<form_key>super_group[' . $associatedProduct->getId() . ']</form_key>'
                        . '</associated_product>';
            }
        }

        $xml .= '</associated_products>';
        return $xml;
    }

    public function addPriceTiers(Mage_Catalog_Model_Product $product, $real_price) {
        $xml = '<price_tiers>';
        try {

            $tiers = $product->getTierPrice();
            foreach ($tiers as $tier) {
                if ($real_price > $tier['website_price']) {
                    $xml .= '<tier>'
                            . '<quantity>' . rtrim(rtrim($tier['price_qty'], '0'), '.') . '</quantity>';
                    if ($product->getTypeId() != 'bundle') {
                        $xml .= '<price>' . Mage::helper('core')
                                        ->currency($tier['price'], true, false) . '</price>';
                    } else {
                        $xml .= '<discount>' . $tier['percent'] . '</discount>';
                    }
                    $xml .= '</tier>';
                }
            }
        } catch (Exception $e) {
            
        }

        $xml .= '</price_tiers>';
        return $xml;
    }

    public function addCustomOptions(Mage_Catalog_Model_Product $product, $real_price) {
        $xml = '<custom_options>';
        try {
            $options = $product->getOptions();

            foreach ($options as $o) {
                if ($o instanceof Mage_Catalog_Model_Product_Option) {
                    $type = $o->getType();
                    switch ($type) {
                        case 'drop_down':
                            $type = 'dropdown';
                            break;
                        case 'field':
                            $type = 'textfield';
                            break;
                        case 'area':
                            $type = 'textarea';
                            break;
                        case 'multiple':
                            $type = 'multiselect';
                            break;
                        case 'date_time':
                            $type = 'datetime';
                            break;
                    }

                    $xml .= '<option><type>' . $type . '</type>'
                            . '<title><![CDATA[' . $o->getTitle() . ']]></title>'
                            . '<is_require>' . $o->getIsRequire() . '</is_require>';

                    if ($type == 'multiselect' || $type == 'checkbox') {
                        $xml .= '<form_key>options[' . $o->getId() . '][]</form_key>';
                    } else {
                        $xml .= '<form_key>options[' . $o->getId() . ']</form_key>';
                    }
                    if ($type == 'textfield' || $type == 'textarea' || $type == 'file'
                            || $type == 'date' || $type == 'datetime' || $type == 'time') {

                        $p = $o->getPrice();
                        if ($p == 0) {
                            $xml .= '<option_price/>';
                        } else {
                            $t = $o->getPriceType();
                            $op = $p;
                            if ($t == 'percent') {
                                $op = $p * $real_price / 100;
                            }
                            $p = Mage::helper('core')
                                            ->currency($op, true, false);
                            if (substr($p, 0, 1) != '-') {
                                $p = '+' . $p;
                            }
                            $xml .= '<option_price><![CDATA[' . $p . ']]></option_price>';
                        }
                    }
                    $xml .= '<items>';

                    $values = $o->getValues();
                    foreach ($values as $item) {
                        $data = $item->getData();
                        $xml .= '<item>'
                                . '<title><![CDATA[' . $data['title'] . ']]></title>';

                        $p = $data['price'];
                        if ($p == 0) {
                            $xml .= '<option_price/>';
                        } else {
                            $t = $data['price_type'];
                            $op = $p;
                            if ($t == 'percent') {
                                $op = $p * $real_price / 100;
                            }
                            $p = Mage::helper('core')
                                            ->currency($op, true, false);
                            if (substr($p, 0, 1) != '-') {
                                $p = '+' . $p;
                            }
                            $xml .= '<option_price><![CDATA[' . $p . ']]></option_price>';
                        }
                        $xml .= '<form_value>' . $item->getId() . '</form_value>';
                        $xml .= '</item>';
                    }
                    $xml .= '</items></option>';
                }
            }
        } catch (Exception $e) {

        }

        $xml .= '</custom_options>';
        return $xml;
    }

    /**
     * Get list attributes of Configuration product
     * 
     * @param Mage_Catalog_Model_Product $product
     */
    public function addConfigurationAttributes(Mage_Catalog_Model_Product $product, $real_price) {
        try {
            $xml = '<configuration_options>';
            $options = $product->getTypeInstance(true)
                            ->getConfigurableAttributesAsArray($product);

            foreach ($options as $o) {
                $xml .= '<option><type>dropdown</type>'
                        . '<title><![CDATA[' . $o['frontend_label'] . ']]></title>'
                        . '<is_require>1</is_require>'
                        . '<form_key>super_attribute[' . $o['attribute_id'] . ']</form_key>'
                        . '<items>';

                $values = $o['values'];
                foreach ($values as $item) {
                    $xml .= '<item>'
                            . '<form_value><![CDATA[' . $item['value_index'] . ']]></form_value>'
                            . '<title><![CDATA[' . $item['store_label'] . ']]></title>';

                    $p = $item['pricing_value'];
                    if (!is_null($p)) {
                        $op = $p;
                        if ($item['is_percent'] == 1) {
                            $op = $p * $real_price / 100;
                        }
                        $p = Mage::helper('core')
                                        ->currency($op, true, false);
                        if (substr($p, 0, 1) != '-') {
                            $p = '+' . $p;
                        }
                        $xml .= '<option_price><![CDATA[' . $p . ']]></option_price>';
                    }
                    $xml .= '</item>';
                }
                $xml .= '</items></option>';
            }

            $xml .= '</configuration_options>';
            return $xml;
        } catch (Exception $e) {
            Zend_Debug::dump($e);
            die();
        }
        return '';
    }

    /**
     * Export Product to XML Data by store code
     *
     * @param string $baseUrl
     * @param string $storeCode
     * @param Mage_Catalog_Model_Product_Collection $collection
     * @param int $totalProducts
     * @param int $totalPages
     * @param int $currentPage
     * @param int $pageSize
     * @param string $orderBy
     * @param string $typeOrder
     */
    public function exportXML($baseUrl, $storeCode, $collection, $totalProducts = 1, $totalPages = 1, $currentPage = 1, $pageSize = 1, $orderBy = 'entity_id', $typeOrder = 'desc') {

        $xml = '<?xml version="1.0"?><root><products>';

        $i = $pageSize * ($currentPage - 1);
        foreach ($collection as $product) {
            $xml .= $this->exportXMLProduct($product, $baseUrl, $storeCode, $i);
        }

        if ($orderBy == 'entity_id') {
            $orderBy = 'new';
        }

        $xml .= '</products>'
                . '<paginator><totalProducts>' . $totalProducts . '</totalProducts>'
                . '<totalPages>' . $totalPages . '</totalPages>'
                . '<currentPage>' . $currentPage . '</currentPage>'
                . '<pageSize>' . $pageSize . '</pageSize>'
                . '<orderBy>' . $orderBy . '</orderBy>'
                . '<typeOrder>' . $typeOrder . '</typeOrder></paginator>'
                . '</root>';

        return $xml;
    }

    /**
     * Loads product list by Category
     *
     * If category has no product and $showProductsInSubCategories = true, then shows
     * all product in sub categories
     *
     * @param string $categoryUrlKey
     * @param string $baseUrl
     * @param string $storeCode
     * @param int $_currentPage
     * @param int $_pageSize
     * @param string $_orderBy
     * @param string $_typeOrder
     * @param boolean $showProductsInSubCategories
     */
    public function getProductListByCategory($categoryUrlKey, $baseUrl, $storeCode, $_currentPage = 1, $_pageSize = 10, $_orderBy = 'new', $_typeOrder = 'desc', $showProductsInSubCategories = false) {
        $store = $this->getStore($baseUrl, $storeCode);
        Mage::app()->setCurrentStore($store);
        $storeId = $store->getId();

        if (is_null($_orderBy) || $_orderBy == '' || $_orderBy == 'new') {
            $_orderBy = 'entity_id';
        }
        $_orderBy = strtolower($_orderBy);

        if (is_null($_typeOrder) || $_typeOrder == '') {
            $_typeOrder = 'desc';
        }
        $_typeOrder = strtolower($_typeOrder);

        if (is_null($_currentPage) || intval($_currentPage) <= 0) {
            $_currentPage = 1;
        }

        if (is_null($_pageSize) || $_pageSize == '') {
            $_pageSize = 10;
        }

        $category = Mage::getModel('catalog/category')
                        ->loadByAttribute('url_key', $categoryUrlKey);

        if ($category instanceof Mage_Catalog_Model_Category && $category->getIsActive()) {
            $totalProducts = count($category
                                    ->setStoreId($storeId)
                                    ->getProductCollection()
                                    ->addAttributeToFilter(array(
                                        array(
                                            'attribute' => 'visibility',
                                            'in' => array(2, 4)
                                        )
                                    ))
            );
            $collection = array();
            if ($totalProducts > 0) {
                $collection = $category->setStoreId($storeId)
                                ->getProductCollection()
                                ->addAttributeToFilter(array(
                                    array(
                                        'attribute' => 'visibility',
                                        'in' => array(2, 4)
                                    )
                                ))
                                ->addAttributeToSort($_orderBy, $_typeOrder)
                                ->setCurPage($_currentPage)
                                ->setPageSize($_pageSize);
            } else if ($showProductsInSubCategories) {
                $children_category_ids = $category->getChildren();
                $resource = Mage::getSingleton("core/resource");
                $read = $resource->getConnection("core_read");

                $sql = Mage::getModel("catalog/product")
                                ->getCollection()
                                ->addStoreFilter($storeId)
                                ->addAttributeToFilter(array(
                                    array(
                                        'attribute' => 'visibility',
                                        'in' => array(2, 4)
                                    )
                                ))
                                ->addAttributeToSort($_orderBy, $_typeOrder)
                                ->getSelect()
                                ->distinct()
                                ->join(array('B' => Mage::getSingleton('core/resource')->getTableName('catalog_category_product')),
                                        'e.entity_id = B.product_id', '')
                                ->where('B.category_id in (?)', split(',', $children_category_ids));

                $rows = $read->fetchAll($sql->__toString());
                $ids = array();
                foreach ($rows as $row) {
                    $ids[] = $row['entity_id'];
                }
                $totalProducts = count($ids);

                $collection = Mage::getModel("catalog/product")
                                ->getCollection()
                                ->addStoreFilter($storeId)
                                ->addAttributeToFilter(array(
                                    array(
                                        'attribute' => 'visibility',
                                        'in' => array(2, 4)
                                    )
                                ))
                                ->addAttributeToSort($_orderBy, $_typeOrder)
                                ->addAttributeToFilter('entity_id', array('in' => $ids))
                                ->setCurPage($_currentPage)
                                ->setPageSize($_pageSize);
            }

            $list = array();
            foreach ($collection as $p) {
//                Zend_Debug::dump($p->getProductUrl());die();
                $product = Mage::getModel('catalog/product')
                                ->setStoreId($storeId)
                                ->load($p->getId());

                $list[] = $product;
            }

            $totalPages = intval($totalProducts / $_pageSize);
            if ($totalProducts % $_pageSize > 0) {
                $totalPages++;
            }

            return $this->exportXML($baseUrl, $store->getCode(), $list, $totalProducts, $totalPages, $_currentPage, $_pageSize, $_orderBy, $_typeOrder);
        } else {
            throw new Magestore_Mageconnector_Exception(Magestore_Mageconnector_Exception::CATEGORY_INVALID);
        }
    }

    /**
     * Searchs Products, if $category_url_key = '' then searchs all product by keyword
     * else searchs product in category has url_key = $category_url_key
     *
     * If category has no product and $showProductsInSubCategories = true, then search
     * all product in sub categories
     *
     * @param string $baseUrl
     * @param string $storeCode
     * @param string $keyword
     * @param string $categoryUrlKey
     * @param int $_currentPage
     * @param int $_pageSize
     * @param string $_orderBy
     * @param string $_typeOrder
     * @param boolean $showProductsInSubCategories
     */
    public function searchProduct($baseUrl, $storeCode, $keyword = '', $categoryUrlKey = '', $_currentPage = 1, $_pageSize = 10, $_orderBy = 'new', $_typeOrder = 'desc', $showProductsInSubCategories = false) {
        $store = $this->getStore($baseUrl, $storeCode);
        Mage::app()->setCurrentStore($store);
        $storeId = $store->getId();

        if (is_null($_currentPage) || $_currentPage == '') {
            $_currentPage = 1;
        }

        if (is_null($_pageSize) || $_pageSize == '') {
            $_pageSize = 10;
        }
        $_start = ((int) $_currentPage - 1) * (int) $_pageSize;

        if (is_null($_orderBy) || $_orderBy == '' || $_orderBy == 'new') {
            $_orderBy = 'entity_id';
        }
        if ($_orderBy == 'price') {
            $_orderBy = 'final_price';
        }

        if (is_null($_typeOrder) || $_typeOrder == '') {
            $_typeOrder = 'desc';
        }

        $_orderBy = strtolower($_orderBy);
        $_typeOrder = strtolower($_typeOrder);

        $resource = Mage::getSingleton("core/resource");
        $read = $resource->getConnection("core_read");
        $listActiveCategory = Mage::getModel('mageconnector/category')
                        ->getListActiveCategoryIdsByStoreView($baseUrl, $storeCode);

        if (is_null($categoryUrlKey) || $categoryUrlKey == '') {
            $sqlGetProductList = Mage::getModel('catalog/product')->getCollection()
                            ->distinct(true)
                            ->addAttributeToSelect('name')
                            ->addStoreFilter($storeId)
                            ->addAttributeToFilter(array(
                                array(
                                    'attribute' => 'name',
                                    'like' => '%' . $this->clean_url($keyword) . '%'
                                ),
                                array(
                                    'attribute' => 'short_description',
                                    'like' => '%' . $this->clean_url($keyword) . '%'
                                )
                            ))
                            ->addAttributeToFilter(array(
                                array(
                                    'attribute' => 'visibility',
                                    'in' => array(3, 4)
                                )
                            ))
                            ->addFinalPrice()
                            ->addFieldToFilter("status", 1)
                            ->getSelect()
                            ->where("`e`.entity_id IN ( SELECT DISTINCT A.product_id FROM " .
                                    Mage::getSingleton('core/resource')->getTableName('catalog_category_product')
                                    . " AS A WHERE A.category_id IN (?) )", $listActiveCategory)
                            ->order($_orderBy . ' ' . $_typeOrder)
                            ->limit($_pageSize, $_start)
            ;
            $productsList = $read->fetchAll($sqlGetProductList);

            $sqlCountFoundProducts = Mage::getModel('catalog/product')->getCollection()
                            ->addStoreFilter($storeId)
                            ->addAttributeToFilter(array(
                                array(
                                    'attribute' => 'name',
                                    'like' => '%' . $this->clean_url($keyword) . '%'
                                ),
                                array(
                                    'attribute' => 'short_description',
                                    'like' => '%' . $this->clean_url($keyword) . '%'
                                )
                            ))
                            ->addAttributeToFilter(array(
                                array(
                                    'attribute' => 'visibility',
                                    'in' => array(3, 4)
                                )
                            ))
                            ->addFieldToFilter("status", 1)
                            ->getSelectCountSql()
                            ->where("`e`.entity_id IN ( SELECT DISTINCT A.product_id FROM " .
                                    Mage::getSingleton('core/resource')->getTableName('catalog_category_product')
                                    . " AS A WHERE A.category_id IN (?) )", $listActiveCategory)
            ;
            $totalProducts = $read->fetchRow($sqlCountFoundProducts);
            foreach ($totalProducts as $v) {
                $totalProducts = $v;
                break;
            }
            if ($totalProducts == 0) {
                throw new Magestore_Mageconnector_Exception(Magestore_Mageconnector_Exception::NO_PRODUCT);
            }
        } else {
            $category = Mage::getModel('catalog/category')
                            ->loadByAttribute('url_key', $categoryUrlKey);

            if (!($category instanceof Mage_Catalog_Model_Category) || !($category->getIsActive())) {
                throw new Magestore_Mageconnector_Exception(Magestore_Mageconnector_Exception::CATEGORY_INVALID);
            }

            $totalProducts = count(
                                    $category->setStoreId($storeId)
                                    ->getProductCollection()
                                    ->addAttributeToFilter(array(
                                        array(
                                            'attribute' => 'visibility',
                                            'in' => array(3, 4)
                                        )
                                    ))
                                    ->addFieldToFilter("status", 1)
            );
            if ($totalProducts > 0) {
                $sqlCountFoundProducts = Mage::getModel('catalog/product')->getCollection()
                                ->addStoreFilter($storeId)
                                ->addAttributeToFilter(array(
                                    array(
                                        'attribute' => 'name',
                                        'like' => '%' . $this->clean_url($keyword) . '%'
                                    ),
                                    array(
                                        'attribute' => 'short_description',
                                        'like' => '%' . $this->clean_url($keyword) . '%'
                                    )
                                ))
                                ->addCategoryFilter($category)
                                ->addAttributeToFilter(array(
                                    array(
                                        'attribute' => 'visibility',
                                        'in' => array(3, 4)
                                    )
                                ))
                                ->addFieldToFilter("status", 1)
                                ->getSelectCountSql();
                $totalProducts = $read->fetchRow($sqlCountFoundProducts);
                foreach ($totalProducts as $v) {
                    $totalProducts = $v;
                    break;
                }

                $sqlGetProductList = Mage::getModel('catalog/product')->getCollection()
                                ->addAttributeToSelect('name')
                                ->addStoreFilter($storeId)
                                ->addAttributeToFilter(array(
                                    array(
                                        'attribute' => 'name',
                                        'like' => '%' . $this->clean_url($keyword) . '%'
                                    ),
                                    array(
                                        'attribute' => 'short_description',
                                        'like' => '%' . $this->clean_url($keyword) . '%'
                                    )
                                ))
                                ->addCategoryFilter($category)
                                ->addAttributeToFilter(array(
                                    array(
                                        'attribute' => 'visibility',
                                        'in' => array(3, 4)
                                    )
                                ))
                                ->addFinalPrice()
                                ->addFieldToFilter("status", 1)
                                ->getSelect()
                                ->order($_orderBy . ' ' . $_typeOrder)
                                ->limit($_pageSize, $_start)
                ;
                $productsList = $read->fetchAll($sqlGetProductList);
            } else if ($showProductsInSubCategories) {
                $children_category_ids = $category->getChildren();
                $active_children_category_ids = array_intersect(split(',', $children_category_ids), $listActiveCategory);

                $resource = Mage::getSingleton("core/resource");
                $read = $resource->getConnection("core_read");

                $sqlCountFoundProducts = Mage::getModel("catalog/product")
                                ->getCollection()
                                ->addStoreFilter($storeId)
                                ->addAttributeToFilter(array(
                                    array(
                                        'attribute' => 'name',
                                        'like' => '%' . $this->clean_url($keyword) . '%'
                                    ),
                                    array(
                                        'attribute' => 'short_description',
                                        'like' => '%' . $this->clean_url($keyword) . '%'
                                    )
                                ))
                                ->addAttributeToFilter(array(
                                    array(
                                        'attribute' => 'visibility',
                                        'in' => array(3, 4)
                                    )
                                ))
                                ->addFieldToFilter("status", 1)
                                ->addAttributeToSort($_orderBy, $_typeOrder)
                                ->getSelectCountSql()
                                ->join(array('B' => Mage::getSingleton('core/resource')->getTableName('catalog_category_product')),
                                        'e.entity_id = B.product_id', '')
                                ->where('B.category_id in (?)', $active_children_category_ids)
                ;

                $totalProducts = $read->fetchRow($sqlCountFoundProducts);
                foreach ($totalProducts as $v) {
                    $totalProducts = $v;
                    break;
                }

                $sqlGetProductList = Mage::getModel("catalog/product")
                                ->getCollection()
                                ->addStoreFilter($storeId)
                                ->addAttributeToFilter(array(
                                    array(
                                        'attribute' => 'visibility',
                                        'in' => array(3, 4)
                                    )
                                ))
                                ->addFinalPrice()
                                ->addFieldToFilter("status", 1)
                                ->getSelect()
                                ->join(array('B' => Mage::getSingleton('core/resource')->getTableName('catalog_category_product')),
                                        'e.entity_id = B.product_id', '')
                                ->where('B.category_id in (?)', $active_children_category_ids)
                                ->order($_orderBy . ' ' . $_typeOrder)
                                ->limit($_pageSize, $_start);
                $productsList = $read->fetchAll($sqlGetProductList);
            } else {
                throw new Magestore_Mageconnector_Exception(Magestore_Mageconnector_Exception::NO_PRODUCT);
            }
        }

        $list = array();
        foreach ($productsList as $p) {
            $product = Mage::getModel('catalog/product')
                            ->setStoreId($storeId)
                            ->load($p['entity_id']);
            $list[] = $product;
        }

        $totalPages = intval($totalProducts / $_pageSize);
        if ($totalProducts % $_pageSize > 0) {
            $totalPages++;
        }

        return $this->exportXML($baseUrl, $store->getCode(), $list, $totalProducts, $totalPages, $_currentPage, $_pageSize, $_orderBy, $_typeOrder);
    }

    /**
     * Get product detail
     *
     * @param string $productUrlKey
     * @param string $baseUrl
     * @param string $storeCode
     * @return string
     */
    public function getProductDetail($productUrlKey, $baseUrl, $storeCode = null) {
        try {
            $store = $this->getStore($baseUrl, $storeCode);
            Mage::app()->setCurrentStore($store);
            $storeId = $store['store_id'];
            $product = Mage::getModel('catalog/product')
                            ->loadByAttribute('url_key', $productUrlKey);

            $product = Mage::getModel('catalog/product')
                            ->setStoreId($store->getId())
                            ->load($product->getId());

            $xml = '<?xml version="1.0"?><root>';
            $i = 0;
            $xml .= $this->exportXMLProduct($product, $baseUrl, $store->getCode(), $i, true);
            $xml .= '</root>';

            return $xml;
        } catch (Exception $e) {
            Zend_Debug::dump($e);
            die();
        }
    }

    public function clean_url($text) {
//        $text = strtolower($text);
//        $code_entities_match = array(' ', '--', '&quot;', '!', '@', '#', '$', '%', '^', '&', '*', '(', ')', '_', '+', '{', '}', '|', ':', '"', '<', '>', '?', '[', ']', '\\', ';', "'", ',', '.', '/', '*', '+', '~', '`', '=');
//        $code_entities_replace = array('-', '-', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '');
//        $text = str_replace($code_entities_match, $code_entities_replace, $text);
        $text = urldecode($text);
        return $text;
    }

}